Examples

Unique Key Example

The following statement is a variation of the statement that created the sample table sh.promotions. It defines inline and implicitly enables a unique key on the promo_id column (other constraints are not shown):

The constraint promo_id_u identifies the promo_id column as a unique key. This constraint ensures that no two promotions in the table have the same ID. However, the constraint does allow promotions without identifiers.

Alternatively, you can define and enable this constraint out of line:

The preceding statement also contains the using_index_clause, which specifies storage characteristics for the index that Oracle creates to enable the constraint.

Composite Unique Key Example

The following statement defines and enables a composite unique key on the combination of the warehouse_id and warehouse_name columns of the oe.warehouses table:

The wh_unq constraint ensures that the same combination of warehouse_id and warehouse_name values does not appear in the table more than once.

The ADD CONSTRAINT clause also specifies other properties of the constraint:

The USING INDEX clause specifies storage characteristics for the index Oracle creates to enable the constraint.

The EXCEPTIONS INTO clause causes Oracle to write to the wrong_id table information about any rows currently in the warehouses table that violate the constraint. If the wrong_id exceptions table does not already exist, then this statement will fail.

Primary Key Example

The following statement is a variation of the statement that created the sample table hr.locations. It creates the locations_demo table and defines and enables a primary key on the location_id column (other constraints from the hr.locations table are omitted):

The loc_id_pk constraint, specified inline, identifies the location_id column as the primary key of the locations_demo table. This constraint ensures that no two locations in the table have the same location number and that no location identifier is NULL.

Alternatively, you can define and enable this constraint out of line:

NOT NULL Example

The following statement alters the locations_demo table (created in "Primary Key Example") to define and enable a NOT NULL constraint on the country_id column:

The constraint country_nn ensures that no location in the table has a null country_id.

Composite Primary Key Example

The following statement defines a composite primary key on the combination of the prod_id and cust_id columns of the sample table sh.sales:

This constraint identifies the combination of the prod_id and cust_id columns as the primary key of the sales table. The constraint ensures that no two rows in the table have the same combination of values for the prod_id column and cust_id columns.

The constraint clause (PRIMARY KEY) also specifies the following properties of the constraint:

The constraint definition does not include a constraint name, so Oracle generates a name for the constraint.

The DISABLE clause causes Oracle to define the constraint but not enable it.

Foreign Key Constraint Example

The following statement creates the dept_20 table and defines and enables a foreign key on the department_id column that references the primary key on the department_id column of the departments table:

The constraint fk_deptno ensures that all departments given for employees in the dept_20 table are present in the departments table. However, employees can have null department numbers, meaning they are not assigned to any department. To ensure that all employees are assigned to a department, you could create a NOT NULL constraint on the department_id column in the dept_20 table in addition to the REFERENCES constraint.

Before you define and enable this constraint, you must define and enable a constraint that designates the department_id column of the departments table as a primary or unique key.

The foreign key constraint definition does not use the FOREIGN KEY clause, because the constraint is defined inline. The data type of the department_id column is not needed, because Oracle automatically assigns to this column the data type of the referenced key.

The constraint definition identifies both the parent table and the columns of the referenced key. Because the referenced key is the primary key of the parent table, the referenced key column names are optional.

Alternatively, you can define this foreign key constraint out of line:

The foreign key definitions in both variations of this statement omit the ON DELETE clause, causing Oracle to prevent the deletion of a department if any employee works in that department.

ON DELETE Example

This statement creates the dept_20 table, defines and enables two referential integrity constraints, and uses the ON DELETE clause:

Because of the first ON DELETE clause, if manager number 2332 is deleted from the employees table, then Oracle sets to null the value of manager_id for all employees in the dept_20 table who previously had manager 2332.

Because of the second ON DELETE clause, Oracle cascades any deletion of a department_id value in the departments table to the department_id values of its dependent rows of the dept_20 table. For example, if Department 20 is deleted from the departments table, then Oracle deletes all of the employees in Department 20 from the dept_20 table.

Composite Foreign Key Constraint Example

The following statement defines and enables a foreign key on the combination of the employee_id and hire_date columns of the dept_20 table:

The constraint fk_empid_hiredate ensures that all the employees in the dept_20 table have employee_id and hire_date combinations that exist in the employees table. Before you define and enable this constraint, you must define and enable a constraint that designates the combination of the employee_id and hire_date columns of the employees table as a primary or unique key.

The EXCEPTIONS INTO clause causes Oracle to write information to the wrong_emp table about any rows in the dept_20 table that violate the constraint. If the wrong_emp exceptions table does not already exist, then this statement will fail.

Check Constraint Examples

The following statement creates a divisions table and defines a check constraint in each column of the table:

Each constraint restricts the values of the column in which it is defined:

check_divno ensures that no division numbers are less than 10 or greater than 99.

check_divname ensures that all division names are in uppercase.

check_office restricts office locations to Dallas, Boston, Paris, or Tokyo.

Because each CONSTRAINT clause contains the DISABLE clause, Oracle only defines the constraints and does not enable them.

The following statement creates the dept_20 table, defining out of line and implicitly enabling a check constraint: